package edu.sjsu.itack.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.sjsu.itack.entity.Board;
import edu.sjsu.itack.entity.Tack;
import edu.sjsu.itack.util.ITackException;

public class TackDao {

	public void createTack(Tack tack) throws ITackException
	{
		String query = "insert into tack(board_id,description,source_url,image_name) values(?,?,?,?);";
		Connection conn = null;

		conn = ConnectionFactory.getInstance().getConnection();

		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setInt(1, tack.getBoardId());
			preparedStatement.setString(2, tack.getDescription());
			preparedStatement.setString(3, tack.getImagePath());
			preparedStatement.setString(4, tack.getImageName());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new ITackException("EXC104", e);
		}	
		finally{
			ConnectionFactory.getInstance().closeConnection(conn);
		}
	}

	public void deleteTack(int tackId) throws ITackException
	{
		String query = "delete from itack.tack where tack_id=?;";
		Connection con = null;
		con = ConnectionFactory.getInstance().getConnection(); 
		try {
			PreparedStatement preparedStatement = con.prepareStatement(query);
			preparedStatement.setInt(1, tackId);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new ITackException("EXC104", e);
		}
		finally
		{
			ConnectionFactory.getInstance().closeConnection(con);
		}
	}

	public Tack[] getAllTacksForBoard(int boardId) throws ITackException
	{
		List<Tack> list = new ArrayList<Tack>();
		String query = "select tack_id,description,source_url,image_name from tack where board_id=" + boardId + ";";
		Connection con = null;
		con = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement preparedStatement = con.prepareStatement(query);
			//preparedStatement.setInt(1, boardId);
			ResultSet resultSet = preparedStatement.executeQuery(query);
			if(resultSet.isBeforeFirst())
			{
				while(resultSet.next())
				{
					Tack tack = new Tack();
					tack.setTackId(resultSet.getInt(1));
					tack.setDescription(resultSet.getString(2));
					tack.setImageName(resultSet.getString(4));
					tack.setImagePath(resultSet.getString(3));
					list.add(tack);
				}
			}	
			return list.toArray(new Tack[0]);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new ITackException("EXC104", e);
		}
		finally{
			ConnectionFactory.getInstance().closeConnection(con);
		}
	}
	public Tack[] getAllTacksForUser(int userId) throws ITackException
	{
		List<Tack> list = new ArrayList<Tack>();
		String query = "SELECT tack.description,board.name, tack.tack_id,tack.image_name,tack.source_url,tack.image_url FROM board INNER JOIN tack ON board.id=tack.board_id where board.user=?;";
		//System.out.println(query);
		Connection con = null;
		con = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement preparedStatement = con.prepareStatement(query);
			preparedStatement.setInt(1, userId);
			ResultSet resultSet = preparedStatement.executeQuery();
			if(resultSet.isBeforeFirst())
			{
				while(resultSet.next())
				{
					Tack tack = new Tack();
					tack.setDescription(resultSet.getString(1));
					tack.setBoardName(resultSet.getString(2));
					tack.setTackId(resultSet.getInt(3));
					tack.setImageName(resultSet.getString(4));
					tack.setUrl(resultSet.getString(5));
					tack.setImagePath(resultSet.getString(6));

					list.add(tack);
				}
			}	
			System.out.println("list size ="+list.size());
			return list.toArray(new Tack[list.size()]);
		} catch (SQLException e) {
			e.getLocalizedMessage();
			//throw new ITackException("EXC104", e);
		}
		finally{
			ConnectionFactory.getInstance().closeConnection(con);
		}
		return null;

	}

	public void updateTack(Tack tack) throws ITackException
	{
		String query = "update itack.tack set board_id=?,description=?,source_url=?,image_name=? where tack_id=?";
		Connection con = null;
		con = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement preparedStatement = con.prepareStatement(query);
			preparedStatement.setInt(1, tack.getBoardId());
			preparedStatement.setString(2, tack.getDescription());
			preparedStatement.setString(3, tack.getUrl());
			preparedStatement.setString(4, tack.getImageName());
			preparedStatement.setInt(5, tack.getTackId());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new ITackException("EXC104", e);
		}
		finally{
			ConnectionFactory.getInstance().closeConnection(con);
		}
	}

}
